use anyhow::Result;
use sqlx::{SqlitePool, sqlite::SqlitePoolOptions};
use uuid::Uuid;
use chrono::Utc;
use chrono_tz::Asia;
use tracing;

use crate::configuration::DatabaseSetting;
use crate::routes::subscriptions::FormData;

#[derive(Debug)]
pub struct SqliteDb{
    pub pool: SqlitePool,
}

impl SqliteDb { 
    pub async fn from_setting(setting: &DatabaseSetting) -> Result<Self> { 
        let pool = SqlitePoolOptions::new()
            .max_connections(setting.max_connections as u32)
            .connect(setting.sqlite_path.as_str())
            .await?;
        let db = Self { pool };
        db.init().await?;
        Ok(db)
    }

    async fn init(&self) -> Result<()> { 
        self.create_subscription().await?;
        Ok(()) 
    }

    async fn create_subscription(&self) -> Result<()> {
        let sql = r#"
            CREATE TABLE IF NOT EXISTS subscriptions (
                id TEXT PRIMARY KEY,
                email TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                subscribed_at NUMBERIC NOT NULL
            )
        "#;
        sqlx::query(sql).execute(&self.pool).await?;
        Ok(())
    }

    #[tracing::instrument(
        name = "Saving new subscriber into the database",
        skip(subscription),
    )]
    pub async fn insert_subscription(&self, subscription: &FormData, request_id: &Uuid) -> Result<()> {
        let sql = r#"
        INSERT INTO subscriptions 
        (id, email, name, subscribed_at) 
        VALUES 
        ($1, $2, $3, $4)
        "#;
        let now = Utc::now().with_timezone(&Asia::Shanghai).timestamp_millis();
        sqlx::query(sql)
            .bind(request_id.to_string().as_str())
            .bind(&subscription.email)
            .bind(&subscription.name)
            .bind(&now)
            .execute(&self.pool)
            .await
            .map_err(|e| {tracing::error!("Failed to execute query: {}", e); e})?;
        Ok(())
    }
}